﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using kim.DBUtility;
using System.Collections.Generic;//Please add references
namespace kim.DAL
{
    /// <summary>
    /// 数据访问类:CClass
    /// </summary>
    public partial class CClass
    {
        public CClass()
        { }
        #region  Method


        public int GetMaxSort(string StrWhere)
        {
            return kim.DBUtility.DbHelperSQL.GetMaxID("Sort", "CClass", StrWhere);
        }

        /// <summary>
        /// 取得指定类别下的列表
        /// </summary>
        /// <param name="ParentID">父ID</param>
        /// <param name="channel_id">频道ID</param>
        /// <returns></returns>
        public DataTable GetChildListT(int ParentID, int channel_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from CClass");
            strSql.Append(" where channelID=" + channel_id + " and ParentID=" + ParentID + " order by Orders asc,id desc");
            DataSet ds = kim.Common.SqlDbHelper.GetDataSet(strSql.ToString(), null);
            return ds.Tables[0];
        }
        /// <summary>
        /// 取得指定类别下的列表                       
        /// </summary>
        /// <param name="ParentID">父ID</param>
        /// <param name="channel_id">频道ID</param>
        /// <returns></returns>
        public DataSet GetChildList(int ParentID, int channel_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from CClass");
            strSql.Append(" where channelID=" + channel_id + " and ParentID=" + ParentID + " order by Orders asc,id desc");
            DataSet ds = kim.Common.SqlDbHelper.GetDataSet(strSql.ToString(), null);
            return ds;
        }

        /// <summary>
        /// 取得所有类别列表
        /// </summary>
        /// <param name="ParentID">父ID</param>
        /// <param name="channel_id">频道ID</param>
        /// <returns></returns>
        public DataTable GetList(int ParentID, int channel_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from CClass");
            strSql.Append(" where channelID=" + channel_id + " order by Orders Desc,id asc");
            DataSet ds = kim.Common.SqlDbHelper.GetDataSet(strSql.ToString(), null);
            DataTable oldData = ds.Tables[0] as DataTable;
            if (oldData == null)
            {
                return null;
            }
            //复制结构
            DataTable newData = oldData.Clone();
            //调用迭代组合成DAGATABLE
            GetChilds(oldData, newData, ParentID, channel_id);
            return newData;
        }

        public DataSet GetNewsPager(string tbname, string FieldKey, int PageCurrent,
int PageSize, string FieldShow, string FieldOrder, string Where)
        {
            SqlParameter[] parameters ={
               new SqlParameter("@tbname",SqlDbType.VarChar,128),
               new SqlParameter("@FieldKey",SqlDbType.VarChar,1000),
               new SqlParameter("@PageCurrent",SqlDbType.Int),
               new SqlParameter("@PageSize",SqlDbType.Int),
               new SqlParameter("@FieldShow",SqlDbType.NVarChar,1000),
               new SqlParameter("@FieldOrder",SqlDbType.NVarChar,1000),
               new SqlParameter("@Where",SqlDbType.NVarChar,1000), 
                new SqlParameter("@ItemCount",SqlDbType.Int),
                new SqlParameter("@PageCount",SqlDbType.Int)};

            parameters[0].Value = tbname;
            parameters[1].Value = FieldKey;
            parameters[2].Value = PageCurrent;
            parameters[3].Value = PageSize;
            parameters[4].Value = FieldShow;
            parameters[5].Value = FieldOrder;
            parameters[6].Value = Where;
            parameters[7].Direction = ParameterDirection.Output;
            parameters[8].Direction = ParameterDirection.Output;
            return kim.Common.SqlDbHelper.GetDataSetProc("sp_PageView", parameters);
        }

        public IList<kim.Model.CClass> GetTopCClass(int Top, string strWhere, string filedOrder)
        {
            IList<Model.CClass> CClass = new List<Model.CClass>();
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select ");
            if (Top > 0)
                strSql.AppendFormat("top {0}", Top);
            strSql.Append("* from CClass");
            if (strWhere != "")
                strSql.AppendFormat(" where {0}", strWhere);
            if (filedOrder != "")
                strSql.AppendFormat(" order by {0}", filedOrder);

            using (SqlDataReader dr = kim.Common.SqlDbHelper.GetReader(strSql.ToString(), null))
            {
                while (dr.Read())
                {
                    Model.CClass model = new Model.CClass();
                    model.ID = Convert.ToInt32(dr["id"]);
                    model.ChannelID = Convert.ToInt32(dr["ChannelID"]);
                    model.Depth = Convert.ToInt32(dr["Depth"]);
                    model.MaxPic = dr["MaxPic"].ToString();
                    model.MinPic = dr["MinPic"].ToString();
                    model.ChildStr = Convert.ToString(dr["ChildStr"]);
                    model.inindex = (bool)dr["inindex"];
                    model.ParentID = Convert.ToInt32(dr["ParentID"]);
                    model.Orders = Convert.ToInt32(dr["Orders"]);
                    model.ClassName = dr["ClassName"].ToString();
                    model.Remark = dr["Remark"].ToString();
                    model.EnName = dr["EnName"].ToString();
                    model.inindex = (bool)dr["inindex"];
                    CClass.Add(model);
                } dr.Close();

            } return CClass;
        }


        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CClass set " + strValue);
            strSql.Append(" where id=" + id);
            int i = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (i > 0)
                return true;
            else
                return false;
        }

        public bool Add(Model.CClass model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CClass (");
            strSql.Append(" ChannelID,RootID,Depth,ClassName,Remark,Orders,ParentID,ParentStr,Child,ChildStr,inindex,xtype,EnName,MinPic,MaxPic)");
            strSql.Append(" values (");
            strSql.Append("@ChannelID,@RootID,@Depth,@ClassName,@Remark,@Orders,@ParentID,@ParentStr,@Child,@ChildStr,@inindex,@xtype,@EnName,@MinPic,@MaxPic)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parmeters ={
                           new SqlParameter("@ChannelID",SqlDbType.Int,4),
                           new SqlParameter("@RootID",SqlDbType.Int,4),
                           new SqlParameter("@Depth",SqlDbType.Int,4),
                           new SqlParameter("@ClassName",SqlDbType.NVarChar,250),
                           new SqlParameter("@Remark",SqlDbType.NVarChar,250),
                           new SqlParameter("@Orders",SqlDbType.Int),
                           new SqlParameter("@ParentID",SqlDbType.Int),
                           new SqlParameter("@ParentStr",SqlDbType.NVarChar,250),
                           new SqlParameter("@Child",SqlDbType.Int),
                           new SqlParameter("@ChildStr",SqlDbType.NVarChar,250),
                           new SqlParameter("@inindex",SqlDbType.Bit,250),
                           new SqlParameter("@xtype",SqlDbType.Int),
                           new SqlParameter("@EnName",SqlDbType.NVarChar,250),
                            new SqlParameter("@MaxPic",SqlDbType.NVarChar,250),
                            new SqlParameter("@MinPic",SqlDbType.NVarChar,250)
                           };

            parmeters[0].Value = model.ChannelID;
            parmeters[1].Value = model.RootID;
            parmeters[2].Value = model.Depth;
            parmeters[3].Value = model.ClassName;
            parmeters[4].Value = model.Remark;
            parmeters[5].Value = model.Orders;
            parmeters[6].Value = model.ParentID;
            parmeters[7].Value = model.ParentStr;
            parmeters[8].Value = model.Child;
            parmeters[9].Value = model.ChildStr;
            parmeters[10].Value = model.inindex;
            parmeters[11].Value = model.xtype;
            parmeters[12].Value = model.EnName;
            parmeters[13].Value = model.MaxPic;
            parmeters[14].Value = model.MinPic;

            object obj1 = kim.Common.SqlDbHelper.GetScalar(strSql.ToString(), parmeters);
            model.ID = Convert.ToInt32(obj1);
            if (model.ParentID > 0)
            {
                Model.CClass model2 = GetModel((int)model.ParentID);
                model.ChildStr = model2.ChildStr + model.ID + ",";
                model.Depth = model2.Depth + 1;
            }
            else
            {
                model.ChildStr = "," + model.ID + ",";
                model.Depth = 1;
            }
            int obj = kim.Common.SqlDbHelper.ExecuteCommand("update CClass set ChildStr='" + model.ChildStr + "', Depth=" + model.Depth + " where id=" + model.ID);
            if (obj > 0)
                return true;
            else
                return false;
        }

        public bool Update(kim.Model.CClass model)
        {  //先判断选中的父节点是否被包含
            if (IsContainNode(model.ID, (int)model.ParentID))
            {
                //查找旧数据
                Model.CClass oldModel = GetModel(model.ID);
                //查找旧父节点数据
                string ChildStr = "," + model.ParentID + ",";
                int ParentID = 1;
                if (oldModel.ParentID > 0)
                {
                    Model.CClass oldParentModel = GetModel((int)oldModel.ParentID); //带事务
                    ChildStr = oldParentModel.ChildStr + model.ParentID + ",";
                    ParentID = (int)oldParentModel.ParentID + 1;
                }
                //先提升选中的父节点
                kim.Common.SqlDbHelper.ExecuteCommand("update CClass set ParentID=" + oldModel.ParentID + ",ChildStr='" + ChildStr + "', ParentID=" + ParentID + " where id=" + model.ParentID); //带事务
                UpdateChilds((int)model.ParentID); //带事务
            }
            //更新子节点
            if (model.ParentID > 0)
            {
                Model.CClass model2 = GetModel((int)model.ParentID); //带事务
                model.ChildStr = model2.ChildStr + model.ID + ",";
                model.ParentID = model2.ParentID + 1;
            }
            else
            {
                model.ChildStr = "," + model.ID + ",";
                model.ParentID = 1;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update CClass set");
            strSql.Append(" ChannelID=@ChannelID,RootID=@RootID,Depth=@Depth,ClassName=@ClassName,Remark=@Remark,Orders=@Orders,ParentID=@ParentID,ParentStr=@ParentStr,Child=@Child,ChildStr=@ChildStr,inindex=@inindex,xtype=@xtype,EnName=@EnName,MaxPic=@MaxPic,MinPic=@MinPic");

            strSql.Append(" where id=@id");
            SqlParameter[] parmeters ={
                             new SqlParameter("@ChannelID",SqlDbType.Int,4),
                           new SqlParameter("@RootID",SqlDbType.Int,4),
                           new SqlParameter("@Depth",SqlDbType.Int,4),
                           new SqlParameter("@ClassName",SqlDbType.NVarChar,250),
                           new SqlParameter("@Remark",SqlDbType.NVarChar,250),
                           new SqlParameter("@Orders",SqlDbType.Int),
                           new SqlParameter("@ParentID",SqlDbType.Int),
                           new SqlParameter("@ParentStr",SqlDbType.NVarChar,250),
                           new SqlParameter("@Child",SqlDbType.Int),
                           new SqlParameter("@ChildStr",SqlDbType.NVarChar,250),
                           new SqlParameter("@inindex",SqlDbType.Bit,250),
                           new SqlParameter("@xtype",SqlDbType.Int),
                           new SqlParameter("@id",SqlDbType.Int),
                           new SqlParameter("@EnName",SqlDbType.NVarChar,250),
                           new SqlParameter("@MaxPic",SqlDbType.NVarChar,250),
                            new SqlParameter("@MinPic",SqlDbType.NVarChar,250)
                                      };
            parmeters[0].Value = model.ChannelID;
            parmeters[1].Value = model.RootID;
            parmeters[2].Value = model.Depth;
            parmeters[3].Value = model.ClassName;
            parmeters[4].Value = model.Remark;
            parmeters[5].Value = model.Orders;
            parmeters[6].Value = model.ParentID;
            parmeters[7].Value = model.ParentStr;
            parmeters[8].Value = model.Child;
            parmeters[9].Value = model.ChildStr;
            parmeters[10].Value = model.inindex;
            parmeters[11].Value = model.xtype;
            parmeters[12].Value = model.ID;
            parmeters[13].Value = model.EnName;
            parmeters[14].Value = model.MaxPic;
            parmeters[15].Value = model.MinPic;
            int obj = kim.Common.SqlDbHelper.ExecuteCommand(strSql.ToString(), parmeters);
            UpdateChilds(model.ID);
            if (obj > 0)
                return true;
            else
                return false;
        }

        public bool Del(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CClass ");
            strSql.Append(" where ChildStr like '%," + id + ",%' ");
            int del = DbHelperSQL.ExecuteSql(strSql.ToString());
            
            StringBuilder strsql1 = new StringBuilder();
            strsql1.AppendFormat("select count(*) from Product ");
            strsql1.AppendFormat(" where ClassID = {0} ", id);
            int con = DbHelperSQL.ExecuteSql(strsql1.ToString());
            if (con > 0)
            {
                StringBuilder strsql2 = new StringBuilder();
                strsql2.Append("delete from CClass ");
                strsql2.AppendFormat(" where ClassID = {0} ", id);
                del = DbHelperSQL.ExecuteSql(strSql.ToString());
            }
            if (del > 0)
                return true;
            else
                return false;
        }
        public Model.CClass GetModel(int id)
        {
            string PARM_ID = "@id";
            Model.CClass model = new Model.CClass();
            string sql = "select * from CClass where id=@id";
            SqlParameter[] parm = new SqlParameter[]{
               new SqlParameter(PARM_ID, SqlDbType.Int,4)};
            parm[0].Value = id;
            using (SqlDataReader dr = kim.Common.SqlDbHelper.GetReader(sql, parm))
            {
                if (dr.Read())
                {
                    model.ID = Convert.ToInt32(dr["id"]);
                    model.ChannelID = Convert.ToInt32(dr["ChannelID"]);
                    model.Depth = Convert.ToInt32(dr["Depth"]);
                    model.ChildStr = Convert.ToString(dr["ChildStr"]);
                    model.inindex = (bool)dr["inindex"];
                    model.ParentID = Convert.ToInt32(dr["ParentID"]);
                    model.Orders = Convert.ToInt32(dr["Orders"]);
                    model.ClassName = dr["ClassName"].ToString();
                    model.Remark = dr["Remark"].ToString();
                    model.EnName = dr["EnName"].ToString();
                    model.MinPic = dr["MinPic"].ToString();
                    model.MaxPic = dr["MaxPic"].ToString();
                    model.InTime = (DateTime)dr["InTime"];
                } dr.Close();
            } return model;
        }
        /// <summary>
        /// 返回类别名称
        /// </summary>
        public string GetTitle(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 title from CClass");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(kim.Common.SqlDbHelper.GetScalar(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "";
            }
            return title;
        }

        #region 私有方法================================

        /// <summary>
        /// 从内存中取得所有下级类别列表（自身迭代）
        /// </summary>
        private void GetChilds(DataTable oldData, DataTable newData, int ParentID, int channel_id)
        {
            DataRow[] dr = oldData.Select("ParentID=" + ParentID);
            for (int i = 0; i < dr.Length; i++)
            {
                //添加一行数据
                DataRow row = newData.NewRow();
                row["id"] = int.Parse(dr[i]["id"].ToString());
                row["channelid"] = int.Parse(dr[i]["channelid"].ToString());
                row["ClassName"] = dr[i]["ClassName"].ToString();
                row["ParentID"] = int.Parse(dr[i]["ParentID"].ToString());
                row["ChildStr"] = dr[i]["ChildStr"].ToString();
                row["Depth"] = Convert.ToInt32(dr[i]["Depth"].ToString());
                row["Orders"] = int.Parse(dr[i]["Orders"].ToString());
                row["Remark"] = dr[i]["Remark"].ToString();
                row["ChildStr"] = dr[i]["ChildStr"].ToString();
                row["EnName"] = dr[i]["EnName"].ToString();
                row["EnName"] = dr[i]["EnName"].ToString();
                row["MinPic"]=dr[i]["MinPic"].ToString();
                row["MaxPic"] = dr[i]["MaxPic"].ToString();

                newData.Rows.Add(row);
                //调用自身迭代
                this.GetChilds(oldData, newData, int.Parse(dr[i]["id"].ToString()), channel_id);
            }
        }

        /// <summary>
        /// 验证节点是否被包含
        /// </summary>
        /// <param name="id">待查询的节点</param>
        /// <param name="ParentID">父节点</param>
        /// <returns></returns>
        private bool IsContainNode(int id, int ParentID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from CClass ");
            strSql.Append(" where ChildStr like '%," + id + ",%' and id=" + ParentID);
            int i = kim.Common.SqlDbHelper.ExecuteCommand(strSql.ToString());
            if (i > 0)
            { return true; }
            else { return false; }
        }

        /// <summary>
        /// 修改子节点的ID列表及深度（自身迭代）
        /// </summary>
        /// <param name="ParentID"></param>
        private void UpdateChilds(int ParentID)
        {
            //查找父节点信息
            Model.CClass model = GetModel(ParentID);
            if (model != null)
            {
                //查找子节点
                string strSql = "select id from CClass where ParentID=" + ParentID;
                DataSet ds = kim.Common.SqlDbHelper.GetDataSet(strSql, null);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    //修改子节点的ID列表及深度
                    int id = int.Parse(dr["id"].ToString());
                    string ChildStr = model.ChildStr + id + ",";
                    int ParentID1 = (int)model.ParentID + 1;
                    kim.Common.SqlDbHelper.ExecuteCommand("update CClass set ChildStr='" + ChildStr + "', ParentID=" + ParentID1 + " where id=" + id);

                    //调用自身迭代
                    this.UpdateChilds(id);
                }
            }
        }
        #endregion

        #region 扩展方法================================
        public int GetParentId(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ParentID from CClass");
            strSql.Append(" where id=" + id);
            return Convert.ToInt32(kim.Common.SqlDbHelper.GetScalar(strSql.ToString()));
        }
        #endregion

        #endregion  Method
    }
}

